ADO Control was designed for use with the ADO connection which is available through Visual Basic 6.0. You must have VB 6.0 or greater to use this control.
If you distribute a program which was written with this control, there are some special notes you need to heed: Regardless of whether you use the ADO Control or you create your ado connections programmatically, any recipient of your software must have ODBC32 installed on their computer and the ODBC connection needs to be created. This connection needs to be the exact same configuration as the one used on the developer's computer.
When you create a new project and paste this control onto any form, you need to have these three refences made for your project. These are: Microsoft ActiveX Data Objects 2.0 Library (msdao20.tlb); Microsoft ActiveX Data Objects Recordset 2.1 Library (msador15.dll); and Microsoft DAO 2.5/3.51 Compatibility Library (dao350.dll). Without these 3 references, your project will crash when you try to run it.
This control uses all commands that the ADO recordset can use, with two exceptions. To read the value of a field from the recordset with ADO, use FieldValue() instead of ADORecordset!Fieldname. To write a value to a field in the recordset, use SetFieldValue() instead of ADORecordset!Fieldname=Value. These functions are discussed below.
Some values for the recordset created with ADO Control are set and cannot be changed. These are: CursorType = adOpenDynamic
LockType = adLockOptimistic
2) Commonly Used Commands
Things of importance for the reader to know:
1) All Methods return either True if the command was executed successfully, False if an error was encountered while executing the command, or the value requested, such as GetString returns a string value.
2) If you receive the error: Invalid Data Type, this means you have asked the control to supply an integer value which is greater than that which the ado recordset will accept. An example: To set the CursorLocation, the valid integers are adUseClient (0)
and adUseServer (1). If you tried to set the CursorLocation to 3, when the project is run, the Invalid Data Type error will occur.
3) Any commands which are not discussed in this section can be viewed in the Visual Basic help file. When you search the help file for the command in question, please ensure you are ready the section for the ADO. Several commands, such as Find, are used with more than one control of DLL file listed in the VB help file.
4) With some ODBC connections, the RecordCount does not work properly. I have not found out why as Microsoft has not published an explination except that if the RecordCount cannot be executed, it returns -1. Therefore, in the event the user needs a recordcount and this method returns a -1, I have added the method LongRecCount. Be advised, this can be very time consuming if accessing a database over the network with several hundred records.
5) The ADO Control does not use a table's fieldname for any functions. Instead, each field in a database is stored in an array. To access the elements of the array, use the Field properties (FieldName(), FieldValue(), and FieldCount()). To determine which field information is being read or entered from, ADO Control begins with the first Field in a database as the 0 element in the array and proceeds to the last.
Commands:
To open the new recordset:
The user must first set the connection string. To access a FoxPro table, for example, the user might use this connection string: ADOControl1.ConnectionString = ";dsn=FoxPro;". To access an AS/400, the user might use this: ADOControl1.ConnectionString = ";dsn=as400;uid=UserID;pswd=Password;".
Please NOTE: For best results, do not include "ODBC" in the begining of the connection. On some ODBC connections this will not.
Next, call the open method: ADOControl1.Open(SQL_Statement). The SQL_Statement can be any valid SQL statement. It is a string, so please insure it is properly quotationed. For example: ADOControl1.Open("select * from TableName")
The Open method returns true if the database was opened, False if the database was not.
To Add a new record to the recordset:
The user will use the Addnew method: ADOControl1.Addnew. The addnew method will automatically perform the Update to the database, as well.
Addnew returns true if the new record was added to the table, False if there was an error.
To find the number of Fields in a database:
The user will use: ADOControl1.FieldCount. The result is returned as Long.
To find the Value stored in a field:
Position the recordset to the record to be read (use the Move methods). Next, the user will use the FieldValue() property. An example of how to read the value stored in each field of a given record is below:
Dim i as long
for i = 0 to ADOControl1.FieldCount - 1
debug.print ADOControl1.FieldValue(i)
next
' this will print the field value in the immediate window
To set the value of a field:
Position the recordset to the record to be set (use the Move methods). Next, the user will use the SetFieldValue() method. An example of how to set the value is below:
Dim i as long
for i = 0 to ADOControl1.FieldCount -1
debug.print ADOControl.SetFieldValue(i, "Test")
next
' this will print True or False in the immediate window
SetFieldValue returns True if the value was properly written to the field, False if an error occured.
SetFieldValue automatically performs the Update function.
Moving the Recordset:
The user will use the Move, MoveNext, MovePrevious, and MoveLast methods to move through the recordset. Example: ADOControl1.MoveNext
The Move methods return true if the move was successful, False if an error occured.
Checking if the Recordset is at the beginning or end of file:
The user will use the BOF and EOF methods, respectively. To check if the recordset is at the beginning of a file: x = ADOControl1.BOF
BOF returns true if at the beginning of file, False if not at the beginning of file.
EOF returns true if at the end of file, False if not at the end of file.
To close the recordset:
The user will use the Close method. ADOControl1.Close
Close returns True if the recordset was closed properly, False if an error occured.
Finding the number of records in a recordset:
The user will use the RecordCount method. example:
Dim x as Long
x = ADOControl1.RecordCount
As discussed above, some ODBC connections will not properly count the records. In this case, RecordCount returns a -1. Should this happen, the user may use the LongRecCount. The only drawback to LongRecCount, is it can be time consuming if accessing a database over a network with more than 400 records in the recordset. Example:
Dim x as Long
x = ADOControl1.LongRecCount
3) Known Bugs
ADO Control has only one known bug. If the user places the value of a field into the caption of the ADO Control and the value stored is the field is empty (Null), ADO Control crashes because the caption property cannot use a Null value.
